package cn.conac.rc.ofs.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.ofs.entity.ApprovalEntity;

@Repository
public interface ApprovalRepository extends GenericDao<ApprovalEntity, Integer> {

	/**
	 * 查询当前部门批文信息
	 * @param orgId
	 * @return 批文信息
	 */
	@Query("select t from ApprovalEntity t, OrgStatusEntity t4, OrganizationBaseEntity b where b.orgId=?1 and b.id=t.id and t.appDate is not null and t.name is not null and t4.status = '03' and t4.updateType <> '24' and t4.updateType <> '40' and t.id = t4.id and t.appDate >= (select max(t2.appDate) from ApprovalEntity t2, OrgStatusEntity t3, OrganizationBaseEntity base where base.id=t2.id and t2.appDate is not null and t2.name is not null and base.orgId = ?1 and t2.isRedefined = 1 and t3.status = '03' and t3.updateType <> '24' and t3.updateType <> '40' and t2.id = t3.id) order by t.appDate DESC, t4.updateTime DESC")
	List<ApprovalEntity> findDetailByOrgId(@Param("orgId") Integer orgId);
	
	/**
	 * 查询部门批文信息（App用）
	 * @param orgId
	 * @return 批文信息
	 */
	@Query("select t from ApprovalEntity t, OrgStatusEntity t2, OrganizationBaseEntity b where b.orgId=?1 and b.id=t.id and t.appDate is not null and t.name is not null and t2.status = '03' and t2.updateType <> '24' and t2.updateType <> '40' and t.id = t2.id order by t.appDate DESC")
	List<ApprovalEntity> findByOrgId(@Param("orgId") Integer orgId);

	/**
	 * 通过OrgId获取批文信息
	 * @param orgId
	 * @return 批文信息
	 */
	@Query("select a from ApprovalEntity a, OrganizationBaseEntity b, OrgStatusEntity s where a.id = b.id and b.id = s.id and s.status = '03' and s.isHistory = '0' and b.orgId = :orgId order by a.appDate desc, s.updateTime desc")
	List<ApprovalEntity> fingApprovalInfoByOrgId(@Param("orgId") Integer orgId);

	/**
	 * 获取最近一次的批文时间
	 * @param orgId
	 * @return 批文信息
	 */
	@Query("select a from ApprovalEntity a, OrganizationBaseEntity b, OrgStatusEntity s where a.id = b.id and b.id = s.id and s.status = '03' and b.orgId = :orgId and a.appDate is not null order by a.appDate desc, s.updateTime desc")
	List<ApprovalEntity> findLatestApprovalDate(@Param("orgId") Integer orgId);
}
